Transactions with Stored Procedures
Database transactions are by no means limited to ADO.NET. In fact,
transactions are powered by the data source. That is to say, when you initiate a
transaction inside ADO.NET, the data source (in this case Microsoft SQL Server)
actually performs all the work.
To illustrate this point, the example in Listing 18.4 demonstrates how to
perform a database transaction inside a stored procedure. It's very similar
to an example used in a previous hour that adds an employee to the Employees
table of the Northwind SQL Server database. This procedure also assigns a
territory to the newly added employee as well, all rolled into a
transaction.
To start a transaction inside a stored procedure in Microsoft SQL Server, you
use the BEGIN TRAN keywords. To roll back a transaction, you use
ROLLBACK TRAN and as you might have guessed, to commit a transaction,
you use COMMIT TRAN.
Listing 18.4 Rolling a Transaction Back to a Saved Point
CREATE PROCEDURE Employee_Add
(
@LastName nvarchar(20),
@FirstName nvarchar(10),
@Title nvarchar(30),
@BirthDate datetime,
@HireDate datetime,
@Address nvarchar(60),
@City nvarchar(15),
@PostalCode nvarchar(10),
@TerritoryID nvarchar(20)
)
AS
DECLARE @iCommunityProductAuditID int
BEGIN TRAN
-- add the main record
INSERT INTO Employees
(
LastName,
FirstName,
Title,
BirthDate,
HireDate,
Address,
City,
PostalCode
)
VALUES
(
@LastName,
@FirstName,
@Title,
@BirthDate,
@HireDate,
@Address,
@City,
@PostalCode
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN @@ERROR
END
-- get EmployeeID
declare @EmployeeID int
SET @EmployeeID = @@IDENTITY
-- add employee to a territory
INSERT INTO EmployeeTerritories
(
EmployeeID,
TerritoryID
)
VALUES
(
@EmployeeID,
@TerritoryID
)
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RETURN @@ERROR
END
COMMIT TRAN
RETURN @@ERROR
GO
The Employee_Add stored procedure in Listing 18.4 accepts a
relatively large list of parameters in lines 3–11. As you'll recall
from Hour 15, "Working with Stored Procedures," these are the same as
function arguments. Line 18 begins a transaction within the stored procedure.
Lines 21–42 add a record into the Employees table using the values supplied
in the parameters.
If any errors were encountered while performing the INSERT query,
the transaction is rolled back using the ROLLBACK TRAN SQL statement in
lines 44–48. In line 52, the automatically incremented identity number
created for the newly added employee is assigned to the @EmployeeID
variable. The @EmployeeID is then used in lines 55–64 to add a
territory for that new employee to the EmployeeTerritories table. Again, lines
66–70 ensure that if any errors were encountered, the entire transaction is
rolled back; this means that not only will the EmployeeTerritories
entry be removed, but also the entry made for the new employee in the Employees
table. On line 72, the transaction is commited.
| 
View Cart
